Re: [SQL] returning the current date in a SQL query

At 21:31 +0300 on 12/8/98, Walt Bigelow wrote:


>
> I have a table (tblprojects) that stores the current projects in our
> facility.  I want to be able to have two of the records "[unknown]" and
> "pending"  (the project titles) always return the current date- basically
> overwriting the date field stored in the table.

You can use the value 'current' for the date field in your table. 'current'
is a value which is always translated in comparisons and other expressions
to the current date and time - as opposed to 'now', which is only current
for the time of insertion.

Here is an example table, with only one field, d, of type datetime:

testing=> select * from test7;
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
(3 rows)

testing=> insert into test7 values ('now');
INSERT 788160 1
testing=> insert into test7 values ('current');
INSERT 788161 1
testing=> select * from test7;
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Sun Aug 16 14:02:53 1998 IDT
current
(5 rows)

As you can see, the row where I entered 'current' displays 'current' rather
than the actual date. This can be overcome easily. But for now, the point
is that the 'current' field stays current. Here, I'll try a query for all
dates after today at 14:00:

testing=> select * from test7
testing-> where d > '1998-08-16 14:00';
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
Sun Aug 16 14:02:53 1998 IDT
current
(3 rows)

As you can see, the row with the 'current' value as well as the one which
was 'now' have been selected. Now, to show the difference, I'll select
everything after today at 14:03. This should drop the row which was 'now':

testing=> select * from test7
testing-> where d > '1998-08-16 14:03';
d
----------------------------
Tue Dec 01 00:00:00 1998 IST
current
(2 rows)

See? Only the 'current' row and the row with the date in the future were
selected. This shows that 'current' continues to update.

So, you ask, how do I get the actual date and not the annoying 'current' in
the output? Very easy. You just use an expression instead of the field -
add zero to the field, and 'current' will be converted to a date:

testing=> select d + '0 days' from test7;
?column?
----------------------------
Tue Dec 01 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Mon Jan 12 00:00:00 1998 IST
Sun Aug 16 14:02:53 1998 IDT
Sun Aug 16 14:13:16 1998 IDT  <---- This is the 'current' row.
(5 rows)

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma